import akshare as ak
import pymysql

class stockName2Id:
    def __init__(self):
        self.sql_connect()

    def sql_connect(self):
        self.stock_db = pymysql.connect(host='127.0.0.1',  # 连接名称，默认127.0.0.1
                                       user='root',  # 用户名
                                       passwd='s159632487',  # 密码
                                       port=3306,  # 端口，默认为3306
                                       db='chujianquant',  # 数据库名称
                                       charset='utf8')  # 字符编码)
    def get_name_id(self):
        state = 1
        stock_zh_a_spot_em_df = ak.stock_zh_a_spot_em()
        for i in range(0,len(stock_zh_a_spot_em_df)):
            id = str(stock_zh_a_spot_em_df['代码'][i])
            name =stock_zh_a_spot_em_df['名称'][i]
            sql = "INSERT INTO stock (stock_id,stock_name) VALUES ('%s', '%s')" % (id, name)
            cursor = self.stock_db.cursor()
            try:
                cursor.execute(sql)
                self.stock_db.commit()
            except:
                state = 0
                cursor.close()
                self.stock_db.close()
                print("error:input failed: %s " % name)
        if state==1 :
            cursor.close()
            self.stock_db.close()
            print("input the data successfully")

    def search_name(self,id):
        sql = "SELECT stock_name FROM stock WHERE stock_id = %s" % (id)
        cursor = self.stock_db.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
            cursor.close()
            self.stock_db.close()
        except:
            cursor.close()
            self.stock_db.close()
            print("the id you search is not included")

    def search_id(self,name : str):
        sql = "SELECT stock_id FROM stock WHERE stock_name = '%s'" % (name)
        cursor = self.stock_db.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
            cursor.close()
            self.stock_db.close()
        except:
            cursor.close()
            self.stock_db.close()
            print("the name you search is not included")







